/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DAO;

import Business.PurchaseDetails_BS;
import Business.Purchase_BS;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 *
 * @author anhvtn_b00545
 */
public class Purchase_DAO extends ConnectionDB{


    Connection conn= null;
    ResultSet rs= null;

    boolean flag= false;

    public List<Purchase_BS> loadPurchaseList()
    {
        List<Purchase_BS> lst;
        lst = new ArrayList<Purchase_BS>();
        conn= Purchase_DAO.getConnection();
        try {
            Statement stmt = conn.createStatement();
            rs= stmt.executeQuery("EXEC spSelectPurchase");
            while(rs.next())
            {
                int pcID = rs.getInt("pcId");
                String sName = rs.getString("sName");
                float paid = rs.getFloat("pPaid");
                float unpaid = rs.getFloat("pUnpaid");
                String date = rs.getString("pcDate");
                lst.add(new Purchase_BS(pcID,sName,paid,unpaid,date));
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return lst;
    }

   public List<PurchaseDetails_BS>  loadPurchaseDetails(int pcID) throws SQLException
   {
       List<PurchaseDetails_BS> listpur;
       listpur = new ArrayList<PurchaseDetails_BS>();
       conn= Purchase_DAO.getConnection();

           CallableStatement cs= conn.prepareCall("{call spSelectPurchaseDetails(?)}");
           cs.setInt(1, pcID);
           rs = cs.executeQuery();
           while(rs.next())
           {
               int purID = rs.getInt("pcID");
               int pID = rs.getInt("pID");
               String pName= rs.getString("pName");
               float pcdPrice = rs.getFloat("pcdPrice");
               int pcdQuantity = rs.getInt("pcdQuatity");
               listpur.add(new PurchaseDetails_BS(purID,pID, pName, pcdPrice, pcdQuantity));
           }
       return listpur;
   }
    public int addPurchase( Purchase_BS p2)
    {
        try
        {
            conn= Purchase_DAO.getConnection();

            int sID = p2.getSID();
            float paid = p2.getPaid();
            float unpaid= p2.getUnpaid();
            String pcDate= p2.getPcDate();

            int newId = 0;

            CallableStatement cs= conn.prepareCall("{call spinsertPurchase (?,?,?,?,?)}");
            cs.registerOutParameter(5, Types.INTEGER);

            cs.setInt(1, sID);
            cs.setFloat(2, paid);
            cs.setFloat(3, unpaid);
            cs.setString(4, pcDate);
            cs.setInt(5, newId);
            if(cs.executeUpdate() == 1)
            {
                return cs.getInt(5);
            }
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
        }
        return -1;
    }

    public boolean addPurchaseDetails(Purchase_BS p3)
    {
        try
        {
            conn = Purchase_DAO.getConnection();

            int pcID= p3.getPCID();
            int pID= p3.getPID();
            float pPrice= p3.getPcPrice();
            int pQuantity= p3.getPcQuantity();
            String pcDate = p3.getPcDate();

            CallableStatement cs= conn.prepareCall("{call spinsertPurchaseDetails (?,?,?,?,?)}");
            cs.setInt(1, pcID);
            cs.setInt(2, pID);
            cs.setFloat(3, pPrice);
            cs.setInt(4, pQuantity);
            cs.setString(5,pcDate);

            cs.executeUpdate();

            flag= true;

        }catch(SQLException ex)
        {
            ex.printStackTrace();
        }

        return flag;
    }

    public boolean updateAddQuantity(Purchase_BS p)
    {
        conn = Purchase_DAO.getConnection();
        try {
            int pID = p.getPID();
            int pQuantity = p.getPcQuantity();

            CallableStatement cs = conn.prepareCall("{call spUpdateAddQuantity(?,?)}");
            cs.setInt(1, pID);
            cs.setInt(2, pQuantity);

            cs.executeUpdate();
            flag =  true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return flag;
    }

    public boolean updatePurchase(Purchase_BS p)
    {
        conn = Purchase_DAO.getConnection();
        try {
            int pcId = p.getPCID();
            float paid = p.getPaid();
            float unpaid = p.getUnpaid();
            String pcDate = p.getPcDate();
            CallableStatement cs = conn.prepareCall("{call spUpdatePurchase(?,?,?,?)}");
            cs.setInt(1, pcId);
            cs.setFloat(2, paid);
            cs.setFloat(3, unpaid);
            cs.setString(4, pcDate);

            cs.executeUpdate();

            flag = true;

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return flag;
    }
    public static boolean remove(int productID){
            return false;

    }

    public static boolean deletPurchase(int pCID) {
         String sql = "{call spDeletePurchase(?)}";
        try{
            CallableStatement cs = ConnectionDB.getConnection().prepareCall(sql);

            cs.setInt(1,pCID);

            return (cs.executeUpdate()==1);
        }catch(Exception ex){
            System.out.println(ex.getMessage());
            return false;
        }
    }

    public List<Purchase_BS> searchPurchaseBySName(String name){
        List<Purchase_BS> listPur = new ArrayList<Purchase_BS>();

        try{
            CallableStatement cs = ConnectionDB.getConnection().prepareCall("{call spSearchPurchaseBySName(?)}");
            cs.setString(1, name);

            ResultSet rsPurchase = cs.executeQuery();
            while(rsPurchase.next())
            {
                int pcID = rsPurchase.getInt("pcId");
                String sName = rsPurchase.getString("sName");
                float paid = rsPurchase.getFloat("pPaid");
                float unpaid = rsPurchase.getFloat("pUnpaid");
                String date = rsPurchase.getString("pcDate");
                listPur.add(new Purchase_BS(pcID,sName,paid,unpaid,date));
            }
        } catch (Exception ex) {
            ex.printStackTrace();

        }
        return listPur;
    }
    public List<Purchase_BS> searchPurchaseByPrice(float price1, float price2){
        List<Purchase_BS> listPur = new ArrayList<Purchase_BS>();

        try{
            CallableStatement cs = ConnectionDB.getConnection().prepareCall("{call spSearchPurchaseBySName(?,?)}");
            cs.setFloat(1, price1);
            cs.setFloat(2, price2);

            ResultSet rsPurchase = cs.executeQuery();
            while(rsPurchase.next())
            {
                int pcID = rsPurchase.getInt("pcId");
                String sName = rsPurchase.getString("sName");
                float paid = rsPurchase.getFloat("pPaid");
                float unpaid = rsPurchase.getFloat("pUnpaid");
                String date = rsPurchase.getString("pcDate");
                listPur.add(new Purchase_BS(pcID,sName,paid,unpaid,date));
            }
        } catch (Exception ex) {
            ex.printStackTrace();

        }
        return listPur;
    }

        public List<Purchase_BS> searchPurchase(String name,float price1, float price2, Date date1, Date date2){
        List<Purchase_BS> listPur = new ArrayList<Purchase_BS>();

        try{
            CallableStatement cs = ConnectionDB.getConnection().prepareCall("{call spSearchPurchase(?,?,?,?,?)}");
            cs.setString(1, name);
            cs.setFloat(2, price1);
            cs.setFloat(3, price2);
            cs.setDate(4, date1 == null ? null : new java.sql.Date(date1.getTime()));
            cs.setDate(5, date2 == null ? null : new java.sql.Date(date2.getTime()));

            ResultSet rsPurchase = cs.executeQuery();
            while(rsPurchase.next())
            {
                int pcID = rsPurchase.getInt("pcId");
                String sName = rsPurchase.getString("sName");
                float paid = rsPurchase.getFloat("pPaid");
                float unpaid = rsPurchase.getFloat("pUnpaid");
                String date = rsPurchase.getString("pcDate");
                listPur.add(new Purchase_BS(pcID,sName,paid,unpaid,date));
            }
        } catch (Exception ex) {
            ex.printStackTrace();

        }
        return listPur;
    }

}














